package com.training.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.training.exceptions.TMSSystemException;
import com.training.vo.Employee;

public class EmployeeDAO {

	static Connection connection = null;

	public static void configDatabase(String DBUrl, String userName,
			String password) throws ClassNotFoundException, SQLException {
		// Step 1 - which database we are using ????

		Class.forName("com.mysql.jdbc.Driver");

		connection = DriverManager.getConnection(DBUrl, userName, password);

	}

	public static Connection getConnection() throws ClassNotFoundException,
			SQLException {

		configDatabase("jdbc:mysql://localhost:3306/tms", "root", "root");

		return connection;
	}

	public List<Employee> getAllUser() throws TMSSystemException {

		Statement statement = null;

		ResultSet resultSet = null;
		try {

			// step 3 - Prepare the statment to that DB

			String query = "SELECT * FROM employee";
			PreparedStatement preparedStatement = getConnection()
					.prepareStatement(query);

			resultSet = preparedStatement.executeQuery();

			List<Employee> employeeList = new ArrayList<Employee>();
			Employee employee = null;

			while (resultSet.next()) {

				employee = new Employee();

				employee.setUserName(resultSet.getString("userName"));

				employee.setPassword(resultSet.getString("password"));

				employee.setFirstName(resultSet
						.getString("employee_first_name"));
				employee.setLastName(resultSet.getString("employee_last_name"));
				employee.setEmployeeId(resultSet.getLong("employee_id"));

				employeeList.add(employee);

			}

			return employeeList;
		} catch (SQLException e) {

			throw new TMSSystemException(
					" Dude , SQL Exception , check DB URL , user name and password");

		} catch (Exception e) {
			throw new TMSSystemException();
		}

		finally {
			try {

				if (statement != null) {
					statement.close();
				}

				if (resultSet != null) {

					resultSet.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}

	}

	public Employee isValidUser(String userNameInput, String passwordInput)
			throws TMSSystemException {

		Statement statement = null;

		ResultSet resultSet = null;
		try {

			// step 3 - Prepare the statment to that DB

			String query = "SELECT * FROM employee WHERE userName = ? AND password =?";
			PreparedStatement preparedStatement = getConnection()
					.prepareStatement(query);

			preparedStatement.setString(2, passwordInput);

			preparedStatement.setString(1, userNameInput);

			resultSet = preparedStatement.executeQuery();

			Employee employee = null;

			while (resultSet.next()) {

				employee = new Employee();

				employee.setUserName(resultSet.getString("userName"));

				employee.setPassword(resultSet.getString("password"));

				employee.setFirstName(resultSet
						.getString("employee_first_name"));
				employee.setLastName(resultSet.getString("employee_last_name"));
				employee.setEmployeeId(resultSet.getLong("employee_id"));
				return employee;

			}
		} catch (SQLException e) {

			throw new TMSSystemException(
					" Dude , SQL Exception , check DB URL , user name and password");

		} catch (Exception e) {
			throw new TMSSystemException();
		}

		finally {
			try {

				if (statement != null) {
					statement.close();
				}

				if (resultSet != null) {

					resultSet.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}

		return null;

	}

	public boolean updateEmployee(Employee employee) throws TMSSystemException {

		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			String query = "UPDATE employee SET employee_first_name=?,employee_last_name=?,password=? WHERE userName=?";

			// step 3 - Prepare the statment to that DB

			preparedStatement = getConnection().prepareStatement(query);

			preparedStatement.setString(1, employee.getFirstName());
			preparedStatement.setString(2, employee.getLastName());
			preparedStatement.setString(3, employee.getPassword());
			preparedStatement.setString(4, employee.getUserName());

			int numberOfRowsUpdated = preparedStatement.executeUpdate();

			if (numberOfRowsUpdated > 0) {
				return true;
			}
		} catch (SQLException e) {

			throw new TMSSystemException(
					" Dude , SQL Exception , check DB URL , user name and password");

		} catch (Exception e) {
			throw new TMSSystemException();
		}

		finally {
			try {

				if (preparedStatement != null) {
					preparedStatement.close();
				}

				if (resultSet != null) {

					resultSet.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}

		return false;

	}

	public boolean registerNewUser(String userNameInput, String passwordInput,
			String firstNameInput, String lastNameInput)
			throws TMSSystemException {

		Statement statement = null;

		ResultSet resultSet = null;
		try {

			// step 3 - Prepare the statment to that DB

			statement = connection.createStatement();

			String query = "insert into tms.employee(employee_id , employee_first_name,employee_last_name , password,userName) values ("
					+ 4
					+ ", '"
					+ firstNameInput
					+ "','"
					+ lastNameInput
					+ "', '" + passwordInput + "','" + userNameInput + "')";
			int numberOfRowsUpdated = statement.executeUpdate(query);

			if (numberOfRowsUpdated > 0) {
				return true;
			}
		} catch (SQLException e) {

			throw new TMSSystemException(
					" Dude , SQL Exception , check DB URL , user name and password");

		} catch (Exception e) {
			throw new TMSSystemException();
		}

		finally {
			try {

				if (statement != null) {
					statement.close();
				}

				if (resultSet != null) {

					resultSet.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}

		return false;

	}

	public static void main(String[] args) throws TMSSystemException {

		new EmployeeDAO().isValidUser(null, null);

	}

}
